#!/bin/bash
# File Name: create_sql_comm.sh
# Author: moshan
# mail: mo_shan@yeah.net
# Created Time: 2019-02-22 16:54:38
# Function: 根据实际配置信息，生成更新ice_db的SQL及回退SQL
#########################################################################
:<<comment
变量注释:
pxc_cluster_port    : pxc集群的mysql端口;
mgr_cluster_port    : mgr集群的mysql端口;
pxc_cluster_vip     : pxc集群的vip;
mgr_cluster_vip     : mgr集群的vip;
pxc_cluster_ip_list : pxc集群的ip列表，以空格分隔;
mgr_cluster_ip_list : mgr集群的ip列表，以空格分隔;
tmp_table_name : 备份server_setting表的表名，更新ice_db前会先将server_setting表进行备份，备份的目标表就是tmp_table_name变量中的表名，即保存更新前的ice_db数据
after_update_table_name  : 备份server_setting表的表名，回退ice_db时会先将server_setting表进行备份，备份的目标表就是after_update_table_name变量中的表名，即保存更新后的ice_db据
对于pxc_cluster_ip_list,mgr_cluster_ip_list两个数组,请严格按照节点角色将ip位置对应好,
如pxc集群的写节点放在pxc_cluster_ip_list的第一个位置,
也要求将mgr的写节点放在mgr_cluster_ip_list的第一个位置,
同理读节点与备份节点也要对应好位置.
最后需要注意的是, pxc集群可能会存在四个节点, 而mgr目前只是三个节点, 这种情况下会需要删除pxc在ice_db的记录, 需要将需要删除的节点ip放在pxc_cluster_ip_list数组最后面
mysql_user： 个人用户的用户名，要求有pxc集群及mgr集群的读权限
mysql_passwd： 个人用户的密码
以下是ice_db的配置，请根据实际情况配置
ice_db_sock_file: ice_db所在的mysql服务器的socket文件绝对路径
ice_db_username:ice_db mysql的用户，要求有读写权限，一般配root
ice_db_password:ice_db mysql的用户的密码
ice_db_name: ice_db的名称，一般是ice_db
ice_db_mysql_comm:是连接ice_db的命令，需要配上码
comment
pxc_cluster_port="13311"
mgr_cluster_port="3373"
pxc_cluster_vip="172.18.8.200"
mgr_cluster_vip="172.18.8.245"
pxc_cluster_ip_list=(172.18.8.30 172.18.8.31 172.18.8.32)
mgr_cluster_ip_list=(172.18.8.30 172.18.8.31 172.18.8.32)
mysql_user="moshan"
mysql_passwd="xxxxxxxxxx"
 
#以下是ice_db的配置，请根据实际情况配置
ice_db_sock_file="/mfw_data/xtradb_G_ko_57/mysql.sock"
ice_db_name="ice_db"
ice_db_username="root"
ice_db_password="xxxxxx"
ice_db_mysql_comm="mysql --socket=${ice_db_sock_file} -u${ice_db_username} -p${ice_db_password} ${ice_db_name}"

#以下不用编辑
table_list_file="table.log"
error_log_file="error.log"
pxc_table_list=(1 2 3)
error_host_count=0
master_sid=0
sql_dir="ice_db_sql"
update_sql="${sql_dir}/update_ice_db.sql"
check_update="${sql_dir}/check_update.sql"
check_commit="${sql_dir}/check_commit.sql"
check_rollback="${sql_dir}/check_rollback.sql"
commit_update="${sql_dir}/commit_update.sql"
rollback_update="${sql_dir}/rollback_update.sql"
main_script="${sql_dir}/start.sh"
reload_ice_db_script="${sql_dir}/reload_cache_for_ice_db.sh"
before_update_table_name="server_setting_backup_before_update_$(date "+%Y_%m_%d")"
after_update_table_name="server_setting_backup_after_update_$(date "+%Y_%m_%d")"
update_table_name="server_setting"
tmp_table_name="${update_table_name}_tmp"

function f_get_reload_cache_for_ice_db_code()
{
        cat <<EOF > ${reload_ice_db_script}
#!/bin/bash
# File Name: start.sh
#########################################################################
mysql_user="${mysql_user}"
mysql_passwd="${mysql_passwd}"
test_table="test_$(date +%s)"
pxc_master_mysql_comm="mysql -u\${mysql_user} -p\${mysql_passwd} -h${pxc_cluster_vip} -P${pxc_cluster_port}"
mgr_master_mysql_comm="mysql -u\${mysql_user} -p\${mysql_passwd} -h${mgr_cluster_vip} -P${mgr_cluster_port}"
pxc_backup_node=\$(\${mgr_master_mysql_comm} -e "show slave status\G" 2>/dev/null|grep "Master_Host:" 2>/dev/null|awk '{print \$2}')
pxc_backup_mysql_comm="mysql -u\${mysql_user} -p\${mysql_passwd} -h\${pxc_backup_node} -P${pxc_cluster_port}"
\${pxc_master_mysql_comm} -e "set global super_read_only=1;set global read_only=1;" 2>/dev/null
read_only_state=\$(\${pxc_master_mysql_comm} -NBe "show variables like 'read_only'" 2>/dev/null|awk '{print \$2}')
super_read_only_state=\$(\${pxc_master_mysql_comm} -NBe "show variables like 'super_read_only'" 2>/dev/null|awk '{print \$2}')
\${pxc_master_mysql_comm} -e "create  database if not exists test;create table if not exists test.\${test_table}(id int primary key);" 2>/dev/null
\${pxc_master_mysql_comm} -e "insert insert into test.\${test_table} select 1" 2>/dev/null
insert_state="\$?"
if [ "\${read_only_state}x" == "ONx" -a "\${super_read_only_state}x" == "ONx" -a "\${insert_state}x" != "0x" ]
then
        echo -e "\033[32m给${pxc_cluster_vip}:${pxc_cluster_port}实例设置read_only成功\033[0m"
    pxc_backup_node_gtid=\$(\${pxc_backup_mysql_comm} -e "show master status\G" 2>/dev/null|grep -A 1000 "Executed_Gtid_Set:" |sed 's/ //g'|tr -d "\n")
    mgr_master_node_gtid=\$(\${mgr_master_mysql_comm} -e "show master status\G" 2>/dev/null|grep -A 1000 "Executed_Gtid_Set:" |sed 's/ //g'|tr -d "\n")
        echo "Source:${pxc_cluster_vip}:${pxc_cluster_port}:GTID:\${pxc_backup_node_gtid}" > gtid.log
    echo "" >> gtid.log
    echo "Destination:${mgr_cluster_vip}:${mgr_cluster_port}:GTID:\${mgr_master_node_gtid}" >> gtid.log
else
        echo -e "\033[31m给${pxc_cluster_vip}:${pxc_cluster_port}实例设置read_only失败\033[0m";
    \${pxc_master_mysql_comm} -NBe "show variables like '%read_only%'" 2>/dev/null
        exit;
fi
if [ "\$(grep -c "^172.18.*" <<< "${pxc_cluster_vip}")x" == "0x" ]
then
    php /mfw_www/app/admin/reloadDbMan.php reload
	/usr/local/python3/bin/python3 ../send.py gtid.log Gtid_record
else
    ice_db_mysql_comm="${ice_db_mysql_comm} -NB"
    echo "insert into ${ice_db_name}.variable_setting set name = 'revision', value = now() ON DUPLICATE KEY UPDATE value = now();"|\${ice_db_mysql_comm}
	python3 ../send.py gtid.log Gtid_record
fi
\${mgr_master_mysql_comm} -e "set global super_read_only=0;set global read_only=0;" 2>/dev/null
for host in ${pxc_cluster_ip_list[@]}
do
        mysql_comm="mysql -u\${mysql_user} -p\${mysql_passwd} -h\${host} -P${pxc_cluster_port}"
        \${mysql_comm} -e "set global super_read_only=1;set global read_only=1;" 2>/dev/null
done
EOF
}



function f_get_start_script_code()
{
cat <<EOF > ${main_script}
#!/bin/bash
# File Name: start.sh
#########################################################################
ice_db_mysql_comm="${ice_db_mysql_comm} -NB"
[ \$? -ne 0 ] && echo -e "\033[31m\n连接ice_db异常，并终止余下操作.\n\033[0m"
choose=\${1}
case \${choose} in
        "update")
                echo -e "\033[32m"
                cat update_ice_db.sql|\${ice_db_mysql_comm}
                echo -e "\033[33m"
                cat check_update.sql|\${ice_db_mysql_comm}
                echo -e "\033[0m"
                ;;
        "commit")
                echo -e "\033[33m"
                cat commit_update.sql|\${ice_db_mysql_comm}
                echo -e "\033[32m"
                cat check_commit.sql|\${ice_db_mysql_comm}
                echo -e "\033[0m"
                ;;
        "rollback")
                echo -e "\033[32m"
                cat rollback_update.sql|\${ice_db_mysql_comm}
                echo -e "\033[33m"
                cat check_rollback.sql|\${ice_db_mysql_comm}
                echo -e "\033[0m"
                ;;
        "reload")
                                bash reload_cache_for_ice_db.sh
                ;;
        *)
                echo "bash ${0} update|commit|rollback|reload"
                ;;
esac
EOF
}


function f_check_table()
{
    #根据pxc的vip获取到集群的表，去检查pxc集群的成员及mgr集群的成员，查看表是否都存在。
    #如果都存在则表示节点配置正常
    check_ip_port_info=(${@})
    for ((node=2;node<${#check_ip_port_info[@]};node++))
    do
        state=1
        mysql_comm="mysql -u${mysql_user} -p${mysql_passwd} -h${check_ip_port_info[${node}]} -P${check_ip_port_info[0]} ${check_ip_port_info[1]}"
        ${mysql_comm} -NBe "show tables;" >${table_list_file} 2>/dev/null
        for ((i=0;i<${#pxc_table_list[@]};i++))
        do
            if [ "$(grep -Ec "^${pxc_table_list[${i}]}$|^${pxc_table_list[${i}]}_[0-9]" ${table_list_file})x" == "0x" ]
            then
                echo -e "\033[31mTable '${check_ip_port_info[1]}.${pxc_table_list[${i}]}' doesn't exist in ${check_ip_port_info[${node}]}:${check_ip_port_info[0]}\033[0m" >> ${error_log_file}
                state=0
                error_host_count=$((${error_host_count}+1))
            fi
        done
        if [ "${state}x" == "1x" ]
        then
            echo -e "\033[32m${check_ip_port_info[${node}]}:${check_ip_port_info[0]} is ok\033[0m"
        else
            echo -e "\033[31m${check_ip_port_info[${node}]}:${check_ip_port_info[0]} is error, please see ${error_log_file}\033[0m"
        fi
    done
}

function f_get_table_list()
{
    #根据pxc集群的vip获取到业务表的列表
    master_sid=$(${ice_db_mysql_comm} -NBe "select sid from ${update_table_name} where host='${pxc_cluster_vip}' and port=${pxc_cluster_port};" 2>/dev/null)
    ${ice_db_mysql_comm} -NBe "select concat(ts.kind,':',ts.db_name) from table_setting ts join ${update_table_name} ss on ts.sid=ss.sid where ss.host='${pxc_cluster_vip}' and ss.port=${pxc_cluster_port};" > ${table_list_file} 2>/dev/null
    pxc_table_list=($(awk -F: '{print $1}' ${table_list_file}))
    if [ "${#pxc_table_list[@]}x" == "0x" ]
    then
        echo -e "\033[31m${pxc_cluster_vip}:${pxc_cluster_port} is error, 请检查pxc集群vip地址或者端口是否正确\033[0m"
        exit
    fi
    db_name="$(awk -F: '{print $2}' ${table_list_file}|tail -1)"
    echo -e "\033[33m下面检查pxc集群成员的ip配置是否正确...\033[0m"
    f_check_table "${pxc_cluster_port}" "${db_name}" "${pxc_cluster_ip_list[@]}"
    echo -e "\033[33m下面检查mgr集群成员的ip配置是否正确...\033[0m"
    f_check_table "${mgr_cluster_port}" "${db_name}" "${mgr_cluster_vip}" "${mgr_cluster_ip_list[@]}"
}

function f_format_print_info()
{
    show_str="${1}"
    if [ "$(grep -cE "^space_str|space_str$|space_strs$" <<< "${show_str}")x" == "1x" ]
    then
        show_str_len=$((${#show_str}-9))
    else
        show_str_len=${#show_str}
    fi
    max_len="${2}"
    tmp_len=$((${max_len}-${show_str_len}))
    for ((var=0;var<${tmp_len};var++))
    do
        if [ "${1}x" == "-x" ]
        then
            show_str="${show_str}-"
        else
            show_str="${show_str}space_str"
        fi
    done
}

function f_enter_str()
{
    var_pos=0
    str_type=(0 0 0)
    clo=(0 0 0)
    for var in ${@}
    do
        eval str_type[${var_pos}]="${var}"
        f_format_print_info "${str_type[${var_pos}]}" "col_len[${var_pos}]"
        eval clo[${var_pos}]="${show_str}"
        var_pos=$((${var_pos}+1))
    done
    show_info=""
    for ((i=0;i<${#clo[@]};i++))
    do
        if [ "${i}x" == "0x" ]
        then
            if [ "${str_type[0]}x" == "-x" ]
            then
                show_info="${show_info}\033[32m+${clo[${i}]}-+-"
            else
                show_info="\033[32m|\033[0m\033[33m${clo[${i}]}\033[0m\033[32m|\033[0m"
            fi
        elif [ "${i}x" == "$((${#clo[@]}-1))x" ]
        then
            if [ "${str_type[0]}x" == "-x" ]
            then
                show_info="${show_info}${clo[${i}]}-+\033[0m"
            else
                show_info="${show_info}\033[33m ${clo[${i}]}\033[0m\033[32m|\033[0m"
            fi
        else
            if [ "${str_type[0]}x" == "-x" ]
            then
                show_info="${show_info}${clo[${i}]}-+-"
            else
                show_info="${show_info}\033[33m ${clo[${i}]}\033[0m \033[32m|\033[0m"
            fi
        fi
    done
    echo -e "${show_info}"|sed 's/space_str/ /g'
}


function f_print_sql()
{
        echo "create table if not exists ${tmp_table_name} like ${update_table_name};"
        echo "drop table if exists ${before_update_table_name};"
        echo "truncate ${tmp_table_name};"
        echo "set interactive_timeout=1000;"
        echo "set wait_timeout=1000;"
        echo "insert into ${tmp_table_name} select * from ${update_table_name};"
        echo "update ${tmp_table_name} set host='${mgr_cluster_vip}',port=${mgr_cluster_port} where master_sid=0 and sid=${master_sid} and host='${pxc_cluster_vip}';"
        for ((i=0;i<${1};i++))
        do
                dbman_sid=$(${ice_db_mysql_comm} -NBe "select sid from ${update_table_name} where host='${pxc_cluster_ip_list[${i}]}' and port=${pxc_cluster_port} and master_sid=${master_sid};")
                echo "update ${tmp_table_name} set host='${mgr_cluster_ip_list[${i}]}',port=${mgr_cluster_port} where master_sid=${master_sid} and sid=${dbman_sid} and host='${pxc_cluster_ip_list[${i}]}' and port=${pxc_cluster_port};"
        done
        if [ ${#pxc_cluster_ip_list[@]} -gt ${#mgr_cluster_ip_list[@]} ]
        then
                count="${2}"
                for ((j=0;j<${count};j++))
                do
                        index=$((${#mgr_cluster_ip_list[@]}+j))
                        dbman_sid=$(${ice_db_mysql_comm} -NBe "select sid from ${update_table_name} where host='${pxc_cluster_ip_list[${index}]}' and port=${pxc_cluster_port} and master_sid=${master_sid};")
                        echo "delete from ${tmp_table_name} where master_sid=${master_sid} and sid=${dbman_sid} and host='${pxc_cluster_ip_list[${index}]}' and port=${pxc_cluster_port};"
                done
        fi
        echo "select '已经按照用户的配置更新了ice_db，下面是使用[${check_update}]来查看更新前后的对比信息';"
        echo "commit;"
}

function f_commit_update()
{
        echo "rename table ${update_table_name} to ${before_update_table_name}, ${tmp_table_name} to ${update_table_name};"
        echo "select '已经提交了更新操作，下面是使[${check_commit}]来查看更新前后的对比信息';"
        echo "commit;"
}

function f_check_commit()
{
        echo "select '已经按照用户的配置更新了ice_db,并提交了更新操作,下面是更新后的信息[table:${update_table_name}]:';"
        echo "select * from ${update_table_name} where master_sid=${master_sid} or sid=${master_sid};"
        echo "select '---------------------------------------------------';"
        echo "select '下面是信息可以理解为是原${update_table_name}表的备份[table:${before_update_table_name}]';"
        echo "select * from ${before_update_table_name} where master_sid=${master_sid} or sid=${master_sid};"
        echo "select '请确认以上信息是否正确, 确认无误后可以执行[bash start.sh reload]命令来更新ice_db的缓存使ice_db的更新生效了';"
}

function f_check_update()
{
        echo "select '已经按照用户的配置更新了ice_db，下面是更新前的信息[table:${update_table_name}]:';"
        echo "select * from ${update_table_name} where master_sid=${master_sid} or sid=${master_sid};"
        echo "select '---------------------------------------------------';"
        echo "select '下面是更新后的信息[table:${tmp_table_name}], 如果结果为空则可能更新失败, 请重新更新:';"
        echo "select * from ${tmp_table_name} where master_sid=${master_sid} or sid=${master_sid};"
        echo "select '请确认以上信息是否正确, 确认无误后使用[bash start.sh commit]来提交更新操作';"
}

function f_update_ice_db()
{
        f_print_sql "${#mgr_cluster_ip_list[@]}" "$((${#pxc_cluster_ip_list[@]}-${#mgr_cluster_ip_list[@]}))"
}

function f_rollback_ice_db()
{
        echo "drop table if exists ${after_update_table_name};"
        echo "rename table ${update_table_name} to ${after_update_table_name},${before_update_table_name} to ${update_table_name};"
        echo "select '已经完成了回滚操作，下面是使用[bash start.sh rollback]来查看回滚后的数据';"
        echo "commit;"
}

function f_check_rollback()
{
        echo "select '已经完成回滚,下面是回滚后的信息[table:${update_table_name}]:';"
        echo "select * from ${update_table_name} where master_sid=${master_sid} or sid=${master_sid};"
        echo "select '---------------------------------------------------';"
        echo "select '下面是信息可以理解为是回滚前${update_table_name}表的备份[table:${after_update_table_name}]';"
        echo "select * from ${after_update_table_name} where master_sid=${master_sid} or sid=${master_sid};"
        echo "select '请确认以上信息是否正确, 确认无误后可以执行[bash start.sh reload]命令来更新ice_db的缓存使回滚生效了';"
}

function f_cluster_print_info()
{
    print_ip_port_info=(${@})
    for ((node=2;node<${#print_ip_port_info[@]};node++))
    do
        if [ "${node}x" == "2x" ]
        then
            role="write"
        elif [ "${node}x" == "$((${#print_ip_port_info[@]}-1))x" ]
        then
            role="backup"
        else
            role="read"
        fi
        f_enter_str "space_str${print_ip_port_info[0]}" "${print_ip_port_info[${node}]}" "${print_ip_port_info[1]}" "${role}space_str"
    done
}

#防止mgr集群的列表与pxc集群的成员重复
for i in ${mgr_cluster_ip_list[@]} ${mgr_cluster_vip}
do
    if [ "$(grep -cw "${i}" <<< "${pxc_cluster_vip} ${pxc_cluster_ip_list[@]}")x" == "1x" ]
    then
        echo
        echo -e "\033[31m${i}\033[33m配置错误，已经存在pxc集群里了VIP:\033[31m${pxc_cluster_vip}\033[33m, cluster_ip_list:\033[31m${pxc_cluster_ip_list[@]}\033[33m，请检查...\033[0m"
        echo
        read -p "请输入[y|Y][n|N]:" a
        if [ "$(grep -ci "^y$" <<< "${a}")x" == "0x" ]
        then
            echo
            echo -e "\033[32m你的输入是[\033[31m${a}\033[32m]即将退出，请重新检查，重新配置\033[0m"
            exit
            echo
        else
            break
        fi
    fi
done

f_get_table_list
if [ "${error_host_count}x" != "0x" ]
then
    echo
    echo -e "\033[33m存在部分节点配置错误，请检查...\033[0m"
    echo
    exit
else
    echo
    echo -e "\033[32m节点配置正常，请确认下面信息...\033[0m"
    echo
fi


col_len=(16 16 6 8)
echo -e "\033[32m切换\033[31m前\033[32mice_db对该集群的配置信息...\033[0m"
f_enter_str "-" "-" "-" "-"
f_enter_str "space_strV_ip" "host_ip" "port" "rolespace_str"
f_enter_str "-" "-" "-" "-"
f_cluster_print_info "${pxc_cluster_vip}" "${pxc_cluster_port}" "${pxc_cluster_ip_list[@]}"
f_enter_str "-" "-" "-" "-"
echo
echo
echo
echo -e "\033[32m切换\033[31m后\033[32mice_db对该集群的配置信息...\033[0m"
f_enter_str "-" "-" "-" "-"
f_enter_str "space_strV_ip" "host_ip" "port" "rolespace_str"
f_enter_str "-" "-" "-" "-"
f_cluster_print_info "${mgr_cluster_vip}" "${mgr_cluster_port}" "${mgr_cluster_ip_list[@]}"
f_enter_str "-" "-" "-" "-"
echo
read -p "以上信息是否正确，请输入你的选择[Y|y]|[N|n]:" choose
if [ "$(grep -cE "^y$|^Y$" <<< "${choose}")x" == "1x" ]
then
    echo
    [ ! -d "${sql_dir}" ] && mkdir ${sql_dir}
    f_update_ice_db > ${update_sql}
    f_rollback_ice_db > ${rollback_update}
    f_commit_update > ${commit_update}
    f_check_commit > ${check_commit}
    f_check_update > ${check_update}
    f_check_rollback > ${check_rollback}
    f_get_start_script_code
        f_get_reload_cache_for_ice_db_code
    echo -e "\033[32m已经生成【更新ice_db，但是不会提交】的sql语句，详情请看[${update_sql}].\033[0m"
    echo -e "\033[32m已经生成【检查ice_db更新前和更新后】的sql语句，详情请看[${check_update}].\033[0m"
    echo
    echo -e "\033[33m已经生成【回滚ice_db】的sql语句，详情请看[${rollback_update}].\033[0m"
    echo -e "\033[33m已经生成检查【回滚ice_db】的sql语句，详情请看[${check_rollback}].\033[0m"
    echo
    echo -e "\033[32m已经生成提交【更新ice_db】的sql语句，详情请看[${commit_update}].\033[0m"
    echo -e "\033[32m已经生成检查【提交更新ice_db】的sql语句，详情请看[${check_commit}].\033[0m"
    echo
    echo -e "\033[32m已经生成检查【刷新ice_db缓存】的脚本，详情请看[${reload_ice_db_script}].\033[0m"
    echo -e "\033[33m已经生成主程序, 详情请看[${main_script}].\033[0m"
    echo
else
    echo
    echo -e "\033[33m人为检查配置有误，即将退出程序，请重新检查后再试。\033[0m"
    echo
fi
